#!/usr/bin/env python
# -*- coding: utf-8 -*-
# Last Update:

'''Excel
'''

__revision__ = '0.1'
__author__ = 'lxd'
import win32com.client
from win32com.client import Dispatch, constants, pywintypes

class Excel(object):
    def __init__(self, visible = True):
        #self.excel = Dispatch('Excel.Application')
        self.excel = win32com.client.gencache.EnsureDispatch('Excel.Application')
        self.excel.Visible = visible
        self.excel.DisplayAlerts = False

    def open(self, filename):
        self.excel.Workbooks.Open(filename)

    def close(self):
        self.excel.Workbooks.Close()

    def quit(self):
        self.excel.Quit()

    def getSheets(self):
        for i in range(self.excel.Worksheets.Count):
            yield self.excel.Worksheets[i]

    def copyOnlyTable(self):
        # only copy columns under the title in A1
        self.excel.Range('A1').Select()
        count = self.excel.Selection.Columns.Count
        endChar = self._getCharOfColumn(count)
        self.excel.Columns('A:%s' %endChar).Select()
        self.excel.Selection.Copy()

    def _getCharOfColumn(self, index):
        return chr(ord('A')+index-1)

    def copyWithoutMoney(self):
        self.delMergeRows()
        self.delMoney()
        self.excel.Columns('A:I').Select()
        self.excel.Selection.Copy()

    def getRowCount(self):
        return self.excel.ActiveSheet.UsedRange.Rows.Count

    def rowHasMerge(self, index):
        #if column 1 has merge return True
        return self.excel.Cells(index+1, 1).MergeCells

    def delMergeRows(self):
        indexes = [i for i in range(self.getRowCount()) if self.rowHasMerge(i)]
        offset = 1
        for i in indexes:
            num = i+offset
            self.excel.Rows('%s:%s' %(num, num)).Select()
            self.excel.Selection.Delete(Shift = constants.xlUp)
            offset -= 1

    def delMoney(self):
        #special
        self.excel.Columns('H:I').Select()
        self.excel.Selection.Delete(Shift = constants.xlToLeft)
        self.excel.Columns('I:I').Select()
        self.excel.Selection.Delete(Shift = constants.xlToLeft)

    def getTitle(self):
        return self.excel.Cells(1, 1).Text

    def getSubTitles(self):
        def is_subtitle(index):
            is_write = self.excel.Range('A%s:A%s' %(index+1, index+1)).Interior.Color == 16777215
            return not is_write
            #self.excel.Rows('%s:%s' %(index+1, index+1)).Select()
            ## 变色是None?
            #return not self.excel.Selection.Interior.ThemeColor

        def merge_list():
            r = [1] * self.getRowCount()
            for i in range(len(r)):
                if self.rowHasMerge(i) and not is_subtitle(i):
                    r[i] = 0
            return r

        def count_list(lst, pos):
            r = 0
            for i in range(pos):
                r += lst[i]
            return r

        lst = merge_list()
        r = []
        for i in range(self.getRowCount()):
            if is_subtitle(i):
                index = count_list(lst, i)
                r.append((index, self.excel.Cells(i+1, 1).Text))
        return r

if __name__ == '__main__':
    import time
    excel = Excel()
    for i in excel.getSubTitles():
        print i
